package com.beyond.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.beanutils.ResultSetDynaClass;
import org.springframework.jdbc.datasource.DataSourceUtils;

import com.beyond.entity.TableConstruct;


public class DataQueryDao {
	
	/**
	 * 查询表的结构信息
	 * @param tableName
	 * @return
	 */
	public List<TableConstruct> getTableColunms(String tableName){
//		return userMapper.getTableColunms(tableName);
		
		return getColumns(tableName);
	}
	

	private Connection getConn() {  
		Connection conn = null;
        String driver = "oracle.jdbc.driver.OracleDriver";  
        String url = "jdbc:oracle:thin:@10.40.63.10:1521:bighead";  
        String username = "gdsignal";  
        String password = "gdsignal";  
        
        try {  
            Class.forName(driver);  
            // new oracle.jdbc.driver.OracleDriver();   
            conn = DriverManager.getConnection(url, username, password);  
        }  
        catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        }  
        catch (SQLException e) {  
            e.printStackTrace();  
        }  
  
        return conn;  
    }
	
	/**
	 * 获取表的记录数
	 * @param tabName[]
	 * @return
	 */
	public int getEtlTabCount(String tabName){
		String sql = "select count(1) as num from "+tabName;
		Connection conn = this.getConn();
		ResultSet rsset = null;
		int cnt1 = 0;
		try {
			rsset = conn.createStatement().executeQuery(sql);
			rsset.next();
			cnt1 = rsset.getInt("num");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rsset != null){
					rsset.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return cnt1;
	}
	
	public void getCountSql(String sql){
		Connection conn = this.getConn();
		ResultSet rs = null;
		try { 
			ResultSetMetaData metaDataRs = conn.createStatement().executeQuery(sql).getMetaData();
			int columns = metaDataRs.getColumnCount();
			
			rs = conn.createStatement().executeQuery(sql);
			while(rs.next()){
				for (int i = 0; i < columns; i++) {
					System.out.println(rs.getInt(i));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs != null)
					rs.close();
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
			
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
		}
	}
	
	
	/**
	 * 根据表名获取表结构信息
	 * @param tableName
	 * @return
	 */
	public List<TableConstruct> getColumns(String tableName){
//		tableName = "F_PS_GDS02_3G_TERMINAL_H";
		List<TableConstruct> list = new ArrayList<TableConstruct>();
		ResultSetMetaData rsMetaData = null;
		Connection conn = this.getConn();//.getConnection();
		String sql = "select * from "+tableName;
		StringBuilder dimention = new StringBuilder();
		StringBuilder target = new StringBuilder();
		String columnName = ""; 
		try {
			rsMetaData = conn.prepareStatement(sql).executeQuery().getMetaData();
			for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
				TableConstruct obj = new TableConstruct();
				obj.setColumnLen(rsMetaData.getColumnDisplaySize(i+1));
				obj.setColumnName(rsMetaData.getColumnName(i+1));
				obj.setDataType(rsMetaData.getColumnTypeName(i+1));
				obj.setDbDataType(rsMetaData.getColumnClassName(i+1));
				list.add(obj);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		return list;
	}
	
	public Map<String,String> getColumnName(String tableName){
		Map<String,String> map = new HashMap<String,String>();
		ResultSetMetaData rsMetaData = null;
		Connection conn = this.getConn();
		String sql = "SELECT * FROM "+tableName;
		StringBuilder dimention = new StringBuilder();
		StringBuilder target = new StringBuilder();
		String columnName = ""; 
		try {
			rsMetaData = conn.prepareStatement(sql).executeQuery().getMetaData();
			for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
				columnName = rsMetaData.getColumnName(i+1);
				if(columnName.contains("0"))
					target.append(columnName).append(",");
				else
					dimention.append(columnName).append(",");
			}
			
			map.put("dimention",dimention.substring(0,dimention.length()-1));
			map.put("target",target.substring(0,target.length()-1));
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		return map;
	}
}


















